<%@ Page Language="C#" Debug="true" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.IO" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Data.Common" %>
<%@ import Namespace="System.Xml" %>
<%@ import Namespace="System.Xml.XPath" %>
<%@ import Namespace="System.Web" %>

<script runat="server">
void Page_Load() {
    
    // Set content type
    Request.ContentType = "text/xml";

    // Read xml stream into a dataset
    DataSet ds = new DataSet();
    ds.ReadXml(Request.InputStream, XmlReadMode.Auto);
    
    // Initialize table index and stored procedure string
    int tableIndex = 0;
    bool StoredProcExists = false;
    bool SearchRequest = false;
    
    // Handle case where first data table is stored procedure
    DataTable sourceData = ds.Tables[tableIndex];
    if (sourceData.TableName == "STOREDPROCEDURE")
    {
        StoredProcExists = true;
        tableIndex++;
        if (Convert.ToString(sourceData.Rows[0].ItemArray[0]).Contains("GetList"))
        {
            SearchRequest = true;
        }
    }

    // Open connection to db
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SuperSignConnectionString"].ConnectionString);
    conn.Open();
    try
    {
        if (!SearchRequest)
        {
            // Loop through tables for bulk copy
            for (int i = tableIndex; i < ds.Tables.Count; i++)
            {
                sourceData = ds.Tables[i];
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn.ConnectionString);
                bulkCopy.DestinationTableName = sourceData.TableName;
                // Loop through columns and establish column mappings
                for (int j = 0; j < sourceData.Columns.Count; j++)
                {
                    string colName = sourceData.Columns[j].ColumnName;
                    bulkCopy.ColumnMappings.Add(colName, colName);
                }
                // Perform bulk copy
                try
                {
                    bulkCopy.WriteToServer(sourceData);
                }
                catch (Exception ex)
                {
                    String err = ex.Message;
                }
            }
        }
        // If stored procedure exists, execute it
        if (StoredProcExists)
        {
            // run stored procedure to process data
            DataTable SProc = ds.Tables[0];
            string sProc = Convert.ToString(SProc.Rows[0].ItemArray[0]);

            // query to get media - temporary
            if (sProc == "sproc_WEB_GetMediaList")
            {
                String sql = "SELECT * FROM MEDIA FOR XML AUTO, ROOT('ROOT')";

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);

                xReader.Close();

            }

            // query to get media - using stored procedure
            if (sProc == "sproc_WEB_GetListMedia")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@FileName";
                param.Value = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.String;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@DateStart";
                if (Convert.ToString(SProc.Rows[0].ItemArray[2]) != "null")
                {
                    String queryDate = Convert.ToString(SProc.Rows[0].ItemArray[2]);
                    queryDate = queryDate.Replace("%2F", "/");
                    param.Value = Convert.ToDateTime(queryDate);
                }
                else
                {
                    param.Value = null;
                }
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);   

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@DateEnd";
                if (Convert.ToString(SProc.Rows[0].ItemArray[3]) != "null")
                {
                    String queryDate = Convert.ToString(SProc.Rows[0].ItemArray[3]);
                    queryDate = queryDate.Replace("%2F", "/");
                    param.Value = Convert.ToDateTime(queryDate);
                }
                else
                {
                    param.Value = null;
                }
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);   

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);

                xReader.Close();

            }

            // query to insert asset
            if (sProc == "sproc_WEB_InsertFromBLAsset")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to update asset
            if (sProc == "sproc_WEB_UpdateFromBLAsset")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to get asset
            if (sProc == "sproc_WEB_GetAsset")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@ID";
                if (Convert.ToString(SProc.Rows[0].ItemArray[1]) == "")
                {
                    param.Value = -1;
                }
                else
                {
                    param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                }
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);

                xReader.Close();

            }

            // query to insert combo
            if (sProc == "sproc_WEB_InsertFromBLCombo")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to update combo
            if (sProc == "sproc_WEB_UpdateFromBLCombo")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to get combo
            if (sProc == "sproc_WEB_GetCombo")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@ID";
                if (Convert.ToString(SProc.Rows[0].ItemArray[1]) == "")
                {
                    param.Value = -1;
                }
                else
                {
                    param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                }
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);

                xReader.Close();

            }

            // query to insert event
            if (sProc == "sproc_WEB_InsertFromBLEvent")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to update event
            if (sProc == "sproc_WEB_UpdateFromBLEvent")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to get event
            if (sProc == "sproc_WEB_GetEvent")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@ID";
                if (Convert.ToString(SProc.Rows[0].ItemArray[1]) == "")
                {
                    param.Value = -1;
                }
                else
                {
                    param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                }
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);

                xReader.Close();

            }

            // query to insert package
            if (sProc == "sproc_WEB_InsertFromBLPackage")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to update package
            if (sProc == "sproc_WEB_UpdateFromBLPackage")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to update package
            if (sProc == "sproc_WEB_UpdateFromBLPackageMultiple")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to save a scheduled package
            if (sProc == "sproc_WEB_SaveFromBLPackage")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set parameter
                param = cmd.CreateParameter();
                param.ParameterName = "@dateFrom";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // set parameter
                param = cmd.CreateParameter();
                param.ParameterName = "@dateTo";
                queryDate = Convert.ToString(SProc.Rows[0].ItemArray[2]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                Response.Write(result);
            }

            // query to update package
            if (sProc == "sproc_WEB_UpdateFromBLSchedule")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@sessionID";
                param.Value = Convert.ToInt32(Session["SessionID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                //// set paramter
                //param = cmd.CreateParameter();
                //param.ParameterName = "@scheduleDates";
                //param.Value = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                //param.DbType = DbType.String;
                //cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to get web control package
            if (sProc == "sproc_WEB_GetPackageLiveControl")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);

                xReader.Close();
            }

            // query to get package
            if (sProc == "sproc_WEB_GetPackage")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@ID";
                if (Convert.ToString(SProc.Rows[0].ItemArray[1]) == "")
                {
                    param.Value = -1;
                }
                else
                {
                    param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                }
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);

                xReader.Close();

            }

            // query to get a list of packages
            if (sProc == "sproc_WEB_GetListPackage")
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sProc, conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    string dsXMLStr = ds.GetXml();
                    //XmlDocument xDoc = new XmlDocument();
                    //xDoc.LoadXml(dsXMLStr);
                    //xDoc.CreateXmlDeclaration("1.0", "utf-8", null);

                    // set paramter
                    DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@XML";
                    //param.Value = xDoc;
                    param.Value = dsXMLStr;
                    param.DbType = DbType.Xml;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "@IncludeDisabled";
                    param.Value = Convert.ToBoolean(SProc.Rows[0].ItemArray[1]);
                    param.DbType = DbType.Boolean;
                    cmd.Parameters.Add(param);

                    // execute the stored procedure and return the results
                    XmlReader xReader = cmd.ExecuteXmlReader();
                    XPathDocument xPathDoc = new XPathDocument(xReader);
                    XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                    Response.ContentType = "text/xml";
                    Response.Write(xPathNav.OuterXml);

                    xReader.Close();
                }
                catch (Exception exception)
                {
                    String err = exception.Message;
                }
            }

            // query to get a list of events
            if (sProc == "sproc_WEB_GetListEvent")
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sProc, conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    string dsXMLStr = ds.GetXml();

                    // set paramter
                    DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@XML";
                    param.Value = dsXMLStr;
                    param.DbType = DbType.Xml;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "@IncludeDisabled";
                    param.Value = Convert.ToBoolean(SProc.Rows[0].ItemArray[1]);
                    param.DbType = DbType.Boolean;
                    cmd.Parameters.Add(param);

                    // execute the stored procedure and return the results
                    XmlReader xReader = cmd.ExecuteXmlReader();
                    XPathDocument xPathDoc = new XPathDocument(xReader);
                    XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                    Response.ContentType = "text/xml";
                    Response.Write(xPathNav.OuterXml);

                    xReader.Close();
                }
                catch (Exception exception)
                {
                    String err = exception.Message;
                }
            }

            // query to get a list of combos
            if (sProc == "sproc_WEB_GetListCombo")
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sProc, conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    string dsXMLStr = ds.GetXml();

                    // set paramter
                    DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@XML";
                    param.Value = dsXMLStr;
                    param.DbType = DbType.Xml;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "@IncludeAssets";
                    param.Value = Convert.ToBoolean(SProc.Rows[0].ItemArray[1]);
                    param.DbType = DbType.Boolean;
                    cmd.Parameters.Add(param);
                    
                    param = cmd.CreateParameter();
                    param.ParameterName = "@IncludeDisabled";
                    param.Value = Convert.ToBoolean(SProc.Rows[0].ItemArray[2]);
                    param.DbType = DbType.Boolean;
                    cmd.Parameters.Add(param);

                    // execute the stored procedure and return the results
                    XmlReader xReader = cmd.ExecuteXmlReader();
                    XPathDocument xPathDoc = new XPathDocument(xReader);
                    XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                    Response.ContentType = "text/xml";
                    Response.Write(xPathNav.OuterXml);

                    xReader.Close();
                }
                catch (Exception exception)
                {
                    String err = exception.Message;
                }
            }

            // query to get a list of assets
            if (sProc == "sproc_WEB_GetListAsset")
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sProc, conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    string dsXMLStr = ds.GetXml();

                    // set paramter
                    DbParameter param = cmd.CreateParameter();
                    param.ParameterName = "@XML";
                    param.Value = dsXMLStr;
                    param.DbType = DbType.Xml;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "@IncludeDisabled";
                    param.Value = Convert.ToBoolean(SProc.Rows[0].ItemArray[1]);
                    param.DbType = DbType.Boolean;
                    cmd.Parameters.Add(param);

                    // execute the stored procedure and return the results
                    XmlReader xReader = cmd.ExecuteXmlReader();
                    XPathDocument xPathDoc = new XPathDocument(xReader);
                    XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                    Response.ContentType = "text/xml";
                    Response.Write(xPathNav.OuterXml);

                    xReader.Close();
                }
                catch (Exception exception)
                {
                    String err = exception.Message;
                }
            }

            // query to get search fields based on level
            if (sProc == "sproc_WEB_GetSearchFields")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@Level";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);

                xReader.Close();

            }

            // query to get scheduled packages by date
            if (sProc == "sproc_WEB_GetScheduledSpotsByDate")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@DateStart";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // increment one day for second parameter
                DateTime dateEnd = Convert.ToDateTime(queryDate).AddDays(1);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@DateEnd";
                param.Value = Convert.ToDateTime(dateEnd);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();
            }


            // query to delete a schedule by date
            if (sProc == "sproc_WEB_DeleteScheduledSpotsByDate")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@DateStart";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // increment one day for second parameter
                DateTime dateEnd = Convert.ToDateTime(queryDate).AddDays(1);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@DateEnd";
                param.Value = Convert.ToDateTime(dateEnd);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(cmd.ExecuteNonQuery());

            }

            // query to see if a name exists already
            if (sProc == "sproc_WEB_DoesNameExist")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@Name";
                param.Value = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.String;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@objLevel";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[2]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));

            }

            // query to get info for an object
            if (sProc == "sproc_WEB_GetInfo")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@ID";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@Level";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[2]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();

            }

            // query to get info for an object
            if (sProc == "sproc_WEB_PackageAlertReport")
            {
                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@PackageID";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@CheckDateTime";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[2]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);


                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();

            }

            // query to duplicate an object
            if (sProc == "sproc_WEB_DuplicateObj")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@objID";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@objLevel";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[2]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@objNewName";
                param.Value = Convert.ToString(SProc.Rows[0].ItemArray[3]);
                param.DbType = DbType.String;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to enable or disable an object
            if (sProc == "sproc_WEB_EnableDisableObj")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@UserID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@ID";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@Level";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[2]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@ThisEnabled";
                param.Value = Convert.ToBoolean(SProc.Rows[0].ItemArray[3]);
                param.DbType = DbType.Boolean;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to update the color of an object
            if (sProc == "sproc_WEB_UpdateColor")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@objID";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@objLevel";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[2]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@color";
                param.Value = Convert.ToString(SProc.Rows[0].ItemArray[3]);
                param.DbType = DbType.String;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to open an object for editing
            if (sProc == "sproc_WEB_ObjForEditOpen")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@objID";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@objLevel";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[2]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();
            }

            // query to close an object for editing
            if (sProc == "sproc_WEB_ObjForEditClose")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@objID";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@objLevel";
                param.Value = Convert.ToInt32(SProc.Rows[0].ItemArray[2]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();
            }

            // query to open a schedule for editing
            if (sProc == "sproc_WEB_ScheduleForEditOpen")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@ScheduleDate";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();
            }

            // query to open a schedule for editing
            if (sProc == "sproc_WEB_ScheduleForEditKeepOpen")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@ScheduleDate";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                cmd.ExecuteNonQuery();
            }


            // query to close an object for editing
            if (sProc == "sproc_WEB_ScheduleForEditClose")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@ScheduleDateStart";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // increment one day for second parameter
                DateTime dateEnd = Convert.ToDateTime(queryDate).AddDays(1);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@ScheduleDateEnd";
                param.Value = Convert.ToDateTime(dateEnd);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@userID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to close all objects and/or schedules for a global redirect or logout
            if (sProc == "sproc_WEB_ObjForEditCloseAll")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@UserID";
                param.Value = Convert.ToInt32(Session["UserID"]);
                param.DbType = DbType.Int32;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }

            // query to test combo string
            if (sProc == "sproc_WEB_TestComboTextString")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@Input";
                param.Value = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                param.DbType = DbType.String;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToString(cmd.ExecuteScalar()));
            }

            // query to get count of start dates
            if (sProc == "sproc_WEB_GetAssetStartDateCountByDate")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@Date";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to get assets with matching start dates
            if (sProc == "sproc_WEB_GetAssetStartDatesByDate")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@Date";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();
            }

            // query to get count of end dates
            if (sProc == "sproc_WEB_GetAssetEndDateCountByDate")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@Date";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                Response.Write(Convert.ToInt32(cmd.ExecuteScalar()));
            }

            // query to get assets with matching end dates
            if (sProc == "sproc_WEB_GetAssetEndDatesByDate")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@Date";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();
            }

            // query to resolve text strings
            if (sProc == "sproc_TEXT_ResolveString")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // set paramter
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@DateTime";
                string queryDate = Convert.ToString(SProc.Rows[0].ItemArray[1]);
                queryDate = queryDate.Replace("%2F", "/");
                param.Value = Convert.ToDateTime(queryDate);
                param.DbType = DbType.DateTime;
                cmd.Parameters.Add(param);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@Input";
                param.Value = Convert.ToString(SProc.Rows[0].ItemArray[2]);
                param.DbType = DbType.String;
                cmd.Parameters.Add(param);

                // set paramter
                DbParameter outputParam = cmd.CreateParameter();
                outputParam.ParameterName = "@Output";
                outputParam.Value = "";
                outputParam.DbType = DbType.String;
                outputParam.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(outputParam);

                // set paramter
                param = cmd.CreateParameter();
                param.ParameterName = "@FormatForWeb";
                param.Value = true;
                param.DbType = DbType.Boolean;
                cmd.Parameters.Add(param);

                try
                {
                    // execute the stored procedure and return the results

                    //StringReader sReader = new StringReader(Convert.ToString(outputParam.Value));
                    String Result = Convert.ToString(cmd.ExecuteScalar());
                    //String Result = Convert.ToString(outputParam.Value);
                    Response.Write(Result);
                }
                catch (Exception ex)
                {
                    String err = ex.Message;
                }
            }

            // query to get list of fonts
            if (sProc == "sproc_WEB_GetTickerTags")
            {

                SqlCommand cmd = new SqlCommand(sProc, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                // execute the stored procedure and return the results
                XmlReader xReader = cmd.ExecuteXmlReader();
                XPathDocument xPathDoc = new XPathDocument(xReader);
                XPathNavigator xPathNav = xPathDoc.CreateNavigator();
                Response.ContentType = "text/xml";
                Response.Write(xPathNav.OuterXml);
                xReader.Close();
            }

        }
        //Close connection
        //conn.Close();
    }
    catch (Exception ex)
    {
        //Utilities.SendErrorLogEmail(ex);  WGO: !!! Unable to find library
        //throw ex;
    }
    finally
    {
        //Close connection
        conn.Close();
    }
}
</script>
